package com.cc.common;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * excel读操作类，用于读取测试用例，安装maven依赖poi、poi-ooxml
 * @author cc
 * @create 2020-04-26-16:54
 */
public class ExcelRead {
    //excel的实例对象
    private Workbook workbook;
    //当前工作的sheet
    private Sheet sheet;
    //当前sheet页的最大行数
    private int rowNum;
    //用于标记excel的类型，默认是xlsx类型
    boolean excelFlg = true;


    /**
     * 构造方法，通过excel的路径打开excel文件
     * @param excelPath
     * @throws IOException
     */
    public ExcelRead(String excelPath){
        File excelFile = new File(excelPath);
        FileInputStream in= null;
        if(!excelFile.isFile()){
            System.out.println("不是文件，请检查");
            return;
        }
        //创建输入流
        try {
            in = new FileInputStream(excelFile);
        } catch (FileNotFoundException e) {
            System.out.println("创建输入流失败");
            e.printStackTrace();
            return;
        }
        if(excelPath.endsWith(".xlsx")){
            excelFlg = true;
        }else if (excelPath.endsWith(".xls")){
            excelFlg = false;
        }else{
            System.out.println("请检查，不是excel类型的文件");
            return;
        }
        //判断什么类型的文件，使用输入流的构造方法创建workbook对象,获取打开的第一个sheet页，并更新最大行数
        if(excelFlg){
            try {
                workbook = new XSSFWorkbook(in);
                sheet = workbook.getSheetAt(0);
                rowNum= sheet.getPhysicalNumberOfRows();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }else{
            try {
                workbook = new HSSFWorkbook(in);
                sheet = workbook.getSheetAt(0);
                rowNum = sheet.getPhysicalNumberOfRows();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        try {
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
        }


    }
    public ExcelRead(){

    }

    /**
     * 通过sheet的index进行sheet切换，并更新该sheet页的最大行数
     * @param index sheet页的index，从0开始
     */
    public void useSheet(int index){
        if(sheet!=null){
           if(workbook!=null){
               sheet = workbook.getSheetAt(index);
               rowNum= sheet.getPhysicalNumberOfRows();
           }
        }
    }

    /**
     * 通过index获取sheet的名称
     * @param index
     * @return sheet的名称
     */
    public String getSheetName(int index){
        String name = "";
        if(workbook!=null){
            name = workbook.getSheetName(index);
        }
        return name;
    }

    /**
     * 获取sheet的个数
     * @return sheet的个数
     */
    public int getSheetNum(){
        int sheetNum = 0;
        if(workbook!=null){
            sheetNum = workbook.getNumberOfSheets();
        }
        return sheetNum;
    }

    /**
     * 获取指定行的内容
     * @param rowNo 指定的行
     * @return
     */
    public List<String> reaLine(int rowNo){
        List<String> line = new ArrayList<String>();
        Row row = sheet.getRow(rowNo);
        //获取该行所有的单元格数量
        int colNum = row.getPhysicalNumberOfCells();
        for(int col=0;col<colNum;col++){
            line.add(getCellValue(row.getCell(col)));
        }
        return line;

    }

    /**
     * 获取某个单元格的值
     * @param cell 单元格
     * @return
     */
    public String getCellValue(Cell cell){
        String cellValue="";
        if(cell==null){
            return cellValue;
        }
        //根据单元格不同值的类型进行获取，最后变成string类型并返回
        CellType cellType = cell.getCellType();
        // 将所有格式转为字符串读取到cellValue
        switch (cellType) {
            case STRING: // 文本
                cellValue = cell.getStringCellValue();
                break;
            case NUMERIC: // 数字、日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    //日期型以年-月-日格式存储
                    SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = fmt.format(cell.getDateCellValue());
                } else {
                    //数字保留两位小数
                    Double d = cell.getNumericCellValue();
                    DecimalFormat df = new DecimalFormat("#.##");
                    cellValue = df.format(d);
                }
                break;
            case BOOLEAN: // 布尔型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK: // 空白
                cellValue = cell.getStringCellValue();
                break;
            case ERROR: // 错误
                cellValue = "错误";
                break;
            case FORMULA: // 公式
                FormulaEvaluator eval;
                if(workbook!=null) {
                    eval = workbook.getCreationHelper().createFormulaEvaluator();
                    cellValue = getCellValue(eval.evaluateInCell(cell));
                }

                break;
            case _NONE:
                cellValue="";
            default:
                cellValue = "错误";
                break;
        }
        return cellValue;
    }

    /**
     * 获取指定的单元格类容
     * @param rowNo
     * @param colNo
     * @return
     */
    public String getCell(int rowNo,int colNo){
        String content = "";
        Row row = sheet.getRow(rowNo);
        content = getCellValue(row.getCell(colNo));
        return content;
    }

    /**
     * 读完关闭
     */
    public void close(){
        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }








}

class TestReader{
    public static void main(String[] args) {
        String excelPath = "G:\\git_project\\AutoTestFrame\\src\\HTTPLogin.xlsx";
        String excelPath1 = "G:\\git_project\\AutoTestFrame\\src\\main\\dailyleaderview.xls";
        ExcelRead read = new ExcelRead(excelPath);
        System.out.println(read.reaLine(3));

    }
}
